﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;

namespace MySqlTool
{

    /// <summary>
    /// 数据库属性
    /// </summary>
    public class MysqlPropertyAttribute : Attribute
    {
        /// <summary>
        /// 属性名
        /// </summary>
        public string Name
        {
            get;
            set;
        }
        public MysqlPropertyAttribute(string name)
        {
            this.Name = name;
        }
    }

    /// <summary>
    /// 数据库助手
    /// </summary>
    public class MySqlManager:IDisposable
    {
        public MySqlManager()
        {
            
        }
        public MySqlManager(string server, string user, string password, string database, string table, int port=3306)
        {
            Table = table;
            Init(server, database, user, password, "", port);
        }

        /// <summary>
        /// 将数据源转化对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row">数据源</param>
        /// <returns></returns>
        public static T RowToObject<T>(DataRow row) where T : new()
        {
            T obj = new T();
            foreach (var pro in typeof(T).GetProperties())
            {
                foreach (MysqlPropertyAttribute a in pro.GetCustomAttributes(typeof(MysqlPropertyAttribute), true))
                {

                    pro.SetValue(obj, row[a.Name]);
                }
            }
            return obj;
        }
        /// <summary>
        /// 产生数据库连接字符串
        /// </summary>
        /// <param name="Server">服务器</param>
        /// <param name="DataBaseName">数据库名</param>
        /// <param name="UserID">用户名</param>
        /// <param name="Password">密码</param>
        /// <param name="port">端口</param>
        /// <returns></returns>
        public static string ConnectionStr(string Server, string DataBaseName, string UserID, string Password, int port = 3306)
        {
            return string.Format("server={0};database={1};user id={2};password={3};port={4}", Server, DataBaseName, UserID, Password, port);
        }
        /// <summary>
        /// 数据库连接
        /// </summary>
        public MySqlConnection Connection
        {
            get;
            set;
        }
        MySqlDataAdapter dataAdapter;
        /// <summary>
        /// 默认命令语句
        /// </summary>
        public string CommandText
        {
            get;
            set;

        }
        /// <summary>
        /// 默认表
        /// </summary>
        public string Table
        {
            get;
            set;
        }
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="server">数据库地址</param>
        /// <param name="database">数据库名</param>
        /// <param name="user">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="selecttext">默认语句</param>
        /// <param name="port">端口</param>
        public void Init(string server, string database, string user, string password, string selecttext = "", int port = 3306)
        {
            Connection = new MySqlConnection(ConnectionStr(server, database, user, password, port));
            CommandText = selecttext;
        }
        void GenAdapter(string command, MySqlConnection connection)
        {
            dataAdapter = new MySqlDataAdapter(command, connection);
            dataAdapter.FillError += DataAdapter_FillError;
            dataAdapter.RowUpdated += DataAdapter_RowUpdated;
            dataAdapter.RowUpdating += DataAdapter_RowUpdating;
        }

        protected virtual void DataAdapter_RowUpdating(object sender, MySqlRowUpdatingEventArgs e)
        {

        }

        protected virtual void DataAdapter_RowUpdated(object sender, MySqlRowUpdatedEventArgs e)
        {

        }
        protected virtual void DataAdapter_FillError(object sender, FillErrorEventArgs e)
        {
            throw new Exception("填充失败");
        }
        /// <summary>
        /// 查找
        /// </summary>
        /// <param name="selecttext">选择语句</param>
        /// <param name="table">表名</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="isDispose">执行完是否释放资源，不释放Dataset</param>
        /// <returns></returns>
        public DataTable Find(string selecttext, string table, MySqlConnection connection, bool isDispose = true)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            GenAdapter(selecttext, connection);
            DataSet set = new DataSet();
            dataAdapter.Fill(set, table);
            var res = set.Tables[table];

            if (isDispose)
            {
                connection.Close();
                connection.Dispose();
                dataAdapter.Dispose();
            }
            return res;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="selecttext">查询语句</param>
        /// <param name="table">查询的表名</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="isDispose">是否查询完即释放资源</param>
        /// <returns></returns>
        public List<T> Find<T>(string selecttext, string table, MySqlConnection connection, bool isDispose = true) where T : new()
        {
            DataTable data= Find(selecttext, table, connection, isDispose);
            List<T> ls = new List<T>();
            foreach (DataRow i in data.Rows)
            {
                T item = RowToObject<T>(i);
                ls.Add(item);
            }
            data.Dispose();
            data.DataSet.Dispose();
            return ls;
        }

        /// <summary>
        /// 根据预设查询语句，对table表进行查询
        /// </summary>
        /// <param name="table"></param>
        /// <param name="isdispose"></param>
        /// <returns></returns>
        public DataTable Find(string table, bool isdispose = true)
        {
            return Find(CommandText, table, Connection, isdispose);
        }

        /// <summary>
        /// 直接查找
        /// </summary>
        /// <param name="select">查找语句</param>
        /// <returns></returns>
        public DataTable Find(string select)
        {
            return Find(select, Table, Connection);
        }


        /// <summary>
        /// 根据默认参数查找
        /// </summary>
        /// <returns></returns>
        public DataTable Find()
        {
            return Find(CommandText, Table, Connection);
        }
        /// <summary>
        /// 以默认参数查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> Find<T>() where T:new()
        {
            return Find<T>(CommandText, Table, Connection);
        }
        /// <summary>
        /// 根据查询语句查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="select">查询语句</param>
        /// <returns></returns>
        public List<T> Find<T>(string select) where T : new()
        {
            return Find<T>(select, Table, Connection);
        }
        /// <summary>
        /// 将对值转化为Object，通过MysqlPropertyAttribute实现
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        static KeyValuePair<string, string>[] GetPairFromObject<T>(T data)
        {
            List<KeyValuePair<string, string>> ls = new List<KeyValuePair<string, string>>();
            foreach (var i in data.GetType().GetProperties())
            {
                var attributes = i.GetCustomAttributes(typeof(MysqlPropertyAttribute), true);
                if (attributes != null)
                {
                    if (attributes.Length > 0)
                    {
                        var a = (MysqlPropertyAttribute)attributes[0];
                        ls.Add(new KeyValuePair<string, string>(a.Name, i.GetValue(data).ToString()));
                    }
                }
            }
            return ls.ToArray();
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="selecttext">查询语句，检验是否已存在</param>
        /// <param name="table">表名</param>
        /// <param name="connection">连接</param>
        /// <param name="datas">数据</param>
        /// <returns>成功更新的数量</returns>
        public int Add<T>(string selecttext, string table, MySqlConnection connection, params T[] datas)
        {
            var t = Find(selecttext, table, connection, false);
            if (t.Rows.Count > 0)
            {
                throw new Exception("已存在值");
            }
            foreach (T i in datas)
            {
                t.Rows.Add(PairsToRow(t, GetPairFromObject(i)));
            }
            return Update(table, t.DataSet, connection);
        }
        /// <summary>
        /// 以默认参数进行添加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="datas">数据</param>
        /// <returns>成功更新的数量</returns>
        public int Add<T>(params T[] datas)
        {
            return Add(CommandText, Table, Connection, datas);
        }
        /// <summary>
        /// 以默认参数进行添加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="select">查询语句，必须返回0条保证无重复</param>
        /// <param name="datas">数据</param>
        /// <returns></returns>
        public int Add<T>(string select,params T[] datas)
        {
            return Add(select, Table, Connection, datas);
        }

        DataRow PairsToRow(DataTable table, KeyValuePair<string, string>[] pairs)
        {
            DataRow row = table.NewRow();
            foreach (var i in pairs)
            {
                row[i.Key] = i.Value;
            }
            return row;
        }
        /// <summary>
        /// 通过对值进行添加
        /// </summary>
        /// <param name="selecttext">选择语句</param>
        /// <param name="table">表名</param>
        /// <param name="connection">连接</param>
        /// <param name="pairs">数据</param>
        /// <returns>成功添加的数量</returns>
        public int Add(string selecttext, string table, MySqlConnection connection, params KeyValuePair<string, string>[] pairs)
        {
            var t = Find(selecttext, table, connection, false);
            if (t.Rows.Count > 0)
            {
                throw new Exception("已存在值");
            }
            t.Rows.Add(PairsToRow(t, pairs));
            return Update(table, t.DataSet, connection);
        }
        /// <summary>
        /// 更新数据库
        /// </summary>
        /// <param name="table"></param>
        /// <param name="data"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        public int Update(string table, DataSet data, MySqlConnection connection)
        {
            MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(dataAdapter);
            int r = dataAdapter.Update(data, table);
            connection.Close();
            dataAdapter.Dispose();
            data.Dispose();
            return r;
        }
        /// <summary>
        /// 根据默认参数进行更新数据库
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int Update(DataSet data)
        {
            return Update(Table, data, Connection);
        }
        /// <summary>
        /// 删除所有选中项目
        /// </summary>
        /// <param name="selecttext">选择语句</param>
        /// <returns>成功删除的条数</returns>
        public int Delete(string selecttext)
        {
            return Delete(selecttext, Table, Connection);
        }
        /// <summary>
        /// 通过默认语句删除所有
        /// </summary>
        /// <returns>成功删除的条数</returns>
        public int DeleteAll()
        {
            return Delete(CommandText, Table, Connection);
        }
        /// <summary>
        /// 删除所有选中项目
        /// </summary>
        /// <param name="selecttext">选择语句</param>
        /// <param name="table">表名</param>
        /// <param name="connection">数据库连接</param>
        /// <returns>成功删除的条数</returns>
        public int Delete(string selecttext, string table, MySqlConnection connection)
        {
            var t = Find(selecttext, table, connection, false);
            for (int i = 0; i < t.Rows.Count; i++)
            {
                t.Rows[i].Delete();
            }
            return Update(table, t.DataSet, connection);
        }
        /// <summary>
        /// 修改数据的若干值
        /// </summary>
        /// <param name="selecttext">选择语句，只能得到一条结果时有效</param>
        /// <param name="table">表名</param>
        /// <param name="connection">连接</param>
        /// <param name="pairs">属性值</param>
        /// <returns></returns>
        public int Alter(string selecttext, string table, MySqlConnection connection, params KeyValuePair<string, string>[] pairs)
        {
            DataTable data = Find(selecttext, table, connection, false);
            if (data != null)
            {
                if (data.Rows.Count == 1)
                {
                    for (int i = 0; i < pairs.Length; i++)
                    {
                        data.Rows[0][pairs[i].Key] = pairs[i].Value;
                    }

                    return Update(table, data.DataSet, connection);
                }
                return -1;
            }
            return -2;
        }
        /// <summary>
        /// 修改某一项
        /// </summary>
        /// <param name="selecttext">选择语句，只能得到一条结果时有效</param>
        /// <param name="key">需要改的的键名</param>
        /// <param name="value">目标值</param>
        /// <returns></returns>
        public int Alter(string selecttext, string key, string value)
        {
            return Alter(selecttext, Table, Connection, new KeyValuePair<string, string>(key, value));
        }

        public void Dispose()
        {
            GC.Collect(GC.MaxGeneration, GCCollectionMode.Forced);
        }
    }


}
